package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.Device;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerDeviceDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String saveGateway = "INSERT INTO `" + databaseTag + "`.`gateway_info` (`id`, `version`, `clientId`, `hardware`, `software`) VALUES (?, ?, ?, ?, ?);";
    private static final String getDeviceQuery = "SELECT * FROM `" + databaseTag + "`.`device` WHERE `device_id` = ? AND `delete_time` is null";
    private static final String getDeviceByUniqueIdQuery = "SELECT * FROM `" + databaseTag + "`.`device` WHERE `device_unique_id` = ?";
    private static final String getDevicesQuery = "SELECT * FROM `" + databaseTag + "`.`device` WHERE `delete_time` is null ORDER BY `device_unique_id`";
    private static final String saveDeviceQuery = "INSERT INTO `" + databaseTag + "`.`device` (`device_id`, `device_unique_id`, `device_model`, `device_type`, `line`,`service_address`,`device_manufactory`,`description`,`device_img`,`device_name`,`group`,`purchase_date`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String updateDeviceQuery = "UPDATE `" + databaseTag + "`.`device` SET `device_unique_id` = ?, `device_model` = ?, `device_type` = ?, `line` = ?, `service_address` = ?, `description` = ?, `device_manufactory`=?,`device_img`=?,`device_name`=?,`group`=?,`purchase_date`=? WHERE `device_id` = ?";
    private static final String updateDeviceLHBTQuery = "UPDATE `" + databaseTag + "`.`device` SET `last_heartbeat_time` = ? WHERE `device_id` = ?";
    private static final String deleteDeviceQuery = "UPDATE `" + databaseTag + "`.`device` SET `delete_time` = ? WHERE `device_id` = ?";
    private static final String getDeviceListQuery = "SELECT * FROM `" + databaseTag + "`.`device` WHERE delete_time is null;";
    private static final String getDeviceByIdQuery = "SELECT * FROM `" + databaseTag + "`.`device` WHERE device_id=?";
    private static final String getDeviceType = "SELECT DISTINCT device_type FROM `" + databaseTag + "`.device order by device_type;";
    private static final String getDeviceModelByDeviceType = "SELECT DISTINCT device_model FROM `" + databaseTag + "`.device where device_type = ? order BY  device_model;";
    private static final String getDeviceUniqueIdByDeviceTypeAndDeviceModel = "SELECT DISTINCT device_unique_id,device_id FROM `" + databaseTag + "`.device where device_type = ? and device_model = ? order by device_unique_id;";
    private static final String findDevicesQuery = "SELECT \n" +
            "    a.device_id,\n" +
            "    a.device_unique_id,\n" +
            "    a.device_model,\n" +
            "    a.device_type,\n" +
            "    a.description,\n" +
            "    c.state\n" +
            "FROM\n" +
            "    `" + databaseTag + "`.device a\n" +
            "        LEFT JOIN\n" +
            "    (SELECT *\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        CASE\n" +
            "                WHEN @d_id != device_id THEN @rownum:=1\n" +
            "                ELSE @rownum:=@rownum + 1\n" +
            "            END AS rownum,\n" +
            "            @d_id:=device_id AS device_id,\n" +
            "            d.time,\n" +
            "            d.state,\n" +
            "            d.source_ip,\n" +
            "            d.write_time\n" +
            "    FROM\n" +
            "        (SELECT @d_id:='') k, (SELECT @rownum:=0) r, `" + databaseTag + "`.device_state d\n" +
            " where d.time >= date_add(now(), interval 1 minute) \n" +
            "    ORDER BY device_id , time DESC) a\n" +
            "WHERE\n" +
            "    rownum = 1 ) c ON a.device_id = c.device_id WHERE a.delete_time IS NULL\n" +
            "order by a.device_unique_id;";
    private static final String findAllDeviceExceptDelete = "SELECT device_id,device_unique_id FROM `" + databaseTag + "`.device where delete_time is null;";
    private static final String getAllDowntimeRecord = "SELECT \n" +
            "    asd.device_unique_id,\n" +
            "    asd.date_id,\n" +
            "    asd.period,\n" +
            "    GROUP_CONCAT(ifnull( asd.user_name,'无') separator '|||') username,\n" +
            "    GROUP_CONCAT(asd.start_time separator '|||') start_time,\n" +
            "    GROUP_CONCAT(asd.end_time separator '|||') end_time,\n" +
            "    GROUP_CONCAT(CONCAT(FLOOR(LTRIM(asd.duration / 3600)),\n" +
            "                '小时',\n" +
            "                FLOOR(LTRIM(asd.duration % 3600 / 60)),\n" +
            "                '分',\n" +
            "                FLOOR(LTRIM(asd.duration % 60)),\n" +
            "                '秒')\n" +
            "        SEPARATOR '|||') duration,\n" +
            "    group_concat(asd.meta_definition separator '|||') meta_definition\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        d.device_unique_id,\n" +
            "            a.date_id,\n" +
            "            CONCAT(s.start_time, '~', s.end_time) period,\n" +
            "            IFNULL(u.user_name, s.shift_lead) user_name,\n" +
            "            a.start_time,\n" +
            "            IF(a.end_time = '00:00:00', '24:00:00', a.end_time) end_time,\n" +
            "            a.duration,\n" +
            "            a.meta_definition\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        device_id,\n" +
            "            DATE(start_time) date_id,\n" +
            "            user_id,\n" +
            "            DATE_FORMAT(start_time, '%H:%i:%s') start_time,\n" +
            "            DATE_FORMAT(end_time, '%H:%i:%s') end_time,\n" +
            "            TIMESTAMPDIFF(SECOND, start_time, end_time) duration,\n" +
            "            b.meta_definition\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.device_idle_info a\n" +
            "    INNER JOIN (SELECT \n" +
            "        *\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.metadata\n" +
            "    WHERE\n" +
            "        meta_key IN ('FaultType' , 'IDLE')) b ON a.idle_reason = b.meta_id UNION ALL SELECT \n" +
            "        device_id,\n" +
            "            DATE(start_time) date_id,\n" +
            "            NULL,\n" +
            "            TIME(start_time) start_time,\n" +
            "            TIME(end_time) end_time,\n" +
            "            TIMESTAMPDIFF(SECOND, start_time, end_time) duration,\n" +
            "            'poweroff'\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.device_state_period\n" +
            "    WHERE\n" +
            "        state = - 1) a\n" +
            "    INNER JOIN (SELECT \n" +
            "            begin_date,\n" +
            "            end_date,\n" +
            "            start_time,\n" +
            "            end_time,\n" +
            "            shift_lead\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.device_shift_relation) s ON s.begin_date <= a.date_id\n" +
            "        AND s.end_date >= a.date_id\n" +
            "        AND s.start_time <= a.start_time\n" +
            "        AND s.end_time >= a.start_time\n" +
            "    INNER JOIN `" + databaseTag + "`.device d ON a.device_id = d.device_id\n" +
            "    LEFT JOIN `" + databaseTag + "`.user u ON a.user_id = u.user_id\n" +
            "    ORDER BY 1 , 2 , 3) asd\n" +
            "GROUP BY asd.device_unique_id , asd.date_id , asd.period;";
    private static final String findAllMessage = "SELECT * FROM `" + databaseTag + "`.message order by create_time desc;";
    private static final String findDeviceAndState = "SELECT \n" +
            "    a.*,\n" +
            "    b.`time`,\n" +
            "    CASE\n" +
            "        WHEN UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(b.time) > 15 THEN '未知'\n" +
            "        WHEN b.state = 0 THEN '不在线'\n" +
            "        WHEN b.state = 1 THEN '在线'\n" +
            "        WHEN b.state = - 1 THEN '计划停机'\n" +
            "        ELSE '未知'\n" +
            "    END AS state\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.`device` a\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        *\n" +
            "    FROM\n" +
            "       `"+databaseTag+"`.`device_state`\n" +
            "    WHERE\n" +
            "        device_state.`status_type` = '0') b ON a.device_id = b.device_id\n" +
            "WHERE\n" +
            "    a.device_id = ?\n" +
            "        AND a.`delete_time` IS NULL\n" +
            "ORDER BY b.`time` DESC\n " +
            "LIMIT 0 , 1;";

    private static final String getDeviceModelDistinctQuery="SELECT distinct device_model FROM `"+databaseTag+"`.device where delete_time is null order by device_model desc;";
    private static final String getDeviceTypeDistinctQuery="SELECT distinct device_type FROM `"+databaseTag+"`.device where delete_time is null order by device_type desc;";
    private static final String getDeviceGroupDistinctQuery="SELECT distinct `group` FROM `"+databaseTag+"`.device where delete_time is null order by `group` desc;";
    private static final String getDeviceLineDistinctQuery="SELECT distinct `line` FROM `"+databaseTag+"`.device where delete_time is null order by line desc;";
    private static final String getLinesByDistinctQuery="SELECT distinct `line` FROM `"+databaseTag+"`.device where delete_time is null and `device_type`=? and `device_model` = ? order by `line` asc;";
    private static final String getGroupsByDistinctQuery="SELECT distinct `group` FROM `"+databaseTag+"`.device where delete_time is null and `device_type`=? and `device_model` = ? order by `group` asc;";
    private static final String findAllMTIdLikeCHarts="SELECT * FROM `"+databaseTag+"`.device WHERE `device_unique_id` LIKE ? ORDER  BY  `device_unique_id` ASC;";
    private static final String findAllIdleLikeCHarts="select * from `"+databaseTag+"`.message where `event_name` like ? order by `event_name` asc;";
    private static final String updateMessageStatus="UPDATE `"+databaseTag+"`.`message` SET `status`= ? WHERE `event_id`= ? and`mtid`= ? and`start_time`= ?;";
    private static final String findAllMTNameLike="SELECT DISTINCT `device_name` FROM `"+databaseTag+"`.device WHERE `device_name` LIKE ? ORDER  BY  `device_name` ASC;";
    private static final String findAllMTTypeLike="SELECT DISTINCT `device_type` FROM `"+databaseTag+"`.device WHERE `device_type` LIKE ? ORDER  BY  `device_type` ASC;";
    private static final String findAllMTModelLike="SELECT DISTINCT `device_model` FROM `"+databaseTag+"`.device WHERE `device_model` LIKE ? ORDER  BY  `device_model` ASC;";
    private static final String findAllMTGroupLike="SELECT DISTINCT `group` FROM `"+databaseTag+"`.device WHERE `group` LIKE ? ORDER  BY  `group` ASC;";
    private static final String findAllMTLineLike="SELECT DISTINCT `line` FROM `"+databaseTag+"`.device WHERE `line` LIKE ? ORDER  BY  `line` ASC;";
    private static final String findAllMTMadeLike="SELECT DISTINCT `device_manufactory` FROM `"+databaseTag+"`.device WHERE `device_manufactory` LIKE ? ORDER  BY  `device_manufactory` ASC;";
    private static final String findAllDistinctGroups="SELECT DISTINCT(`group`) FROM `"+databaseTag+"`.`device` WHERE `delete_time` is null ORDER BY `group`;";
    private static final String findAllDistinctLines="SELECT DISTINCT(`line`) FROM `"+databaseTag+"`.`device` WHERE `delete_time` is null ORDER BY `line`;";

    public Device getDevice(String database, String deviceId) {
        try {
            return jdbcTemplate.queryForObject(getDeviceQuery.replace(databaseTag, database), new Object[]{deviceId}, new int[]{Types.VARCHAR}, Device.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public Device getDeviceByUniqueId(String database, String uniqueId) {
        try {
            return jdbcTemplate.queryForObject(getDeviceByUniqueIdQuery.replace(databaseTag, database), new Object[]{uniqueId}, new int[]{Types.VARCHAR}, Device.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<Map<String, Object>> getDevices(String database) {
        return jdbcTemplate.queryForList(findDevicesQuery.replace(databaseTag, database));
    }

    public void updateDevice(String database, Device device) {
        jdbcTemplate.update(updateDeviceQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, device.getDeviceUniqueId());
                    pss.setString(2, device.getDeviceModel());
                    pss.setString(3, device.getDeviceType());
                    pss.setString(4, device.getLine());
                    pss.setString(5, device.getServiceAddress());
                    pss.setString(6, device.getDescription());
                    pss.setString(7, device.getDeviceManufactory());
                    pss.setString(8, device.getDeviceImg());
                    pss.setString(9, device.getDeviceName());
                    pss.setString(10, device.getGroup());
                    pss.setString(11, device.getPurchaseDate());
                    pss.setString(12, device.getDeviceId());
                }
        );
    }

    public void saveDevice(String database, Device device) {
        jdbcTemplate.update(saveDeviceQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, device.getDeviceId());
                    pss.setString(2, device.getDeviceUniqueId());
                    pss.setString(3, device.getDeviceModel());
                    pss.setString(4, device.getDeviceType());
                    pss.setString(5, device.getLine());
                    pss.setString(6, device.getServiceAddress());
                    pss.setString(7, device.getDeviceManufactory());
                    pss.setString(8, device.getDescription());
                    pss.setString(9, device.getDeviceImg());
                    pss.setString(10, device.getDeviceName());
                    pss.setString(11, device.getGroup());
                    pss.setString(12, device.getPurchaseDate());
                }
        );
    }

    public void deleteDevice(String database, String deviceId) {
        jdbcTemplate.update(deleteDeviceQuery.replace(databaseTag, database), new Object[]{new java.sql.Date(new java.util.Date().getTime()), deviceId}, new int[]{Types.DATE, Types.VARCHAR});
    }

    public List<Device> getDeviceList(String database) {
        return jdbcTemplate.query(getDeviceListQuery.replace(databaseTag, database), Device.getRowMapper());
    }

    public Device getDeviceByDeviceId(String database, String deviceId) {
        try {
            return jdbcTemplate.queryForObject(getDeviceByIdQuery.replace(databaseTag, database), new Object[]{deviceId}, Device.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<Device> findAll(String database) {
        return jdbcTemplate.query(getDevicesQuery.replace(databaseTag, database), Device.getRowMapper());
    }

    public List<String> getDeviceType(String database) {
        return jdbcTemplate.queryForList(getDeviceType.replace(databaseTag, database), String.class);
    }

    public List<String> getDeviceModelByType(String database, String deviceType) {
        return jdbcTemplate.queryForList(getDeviceModelByDeviceType.replace(databaseTag, database), String.class, deviceType);
    }

    public List<Map<String, Object>> getDeviceUniqueIdByTypeAndModel(String database, String deviceType, String deviceModel) {
        return jdbcTemplate.queryForList(getDeviceUniqueIdByDeviceTypeAndDeviceModel.replace(databaseTag, database), deviceType, deviceModel);
    }

    public List<Map<String, Object>> findAllDeviceExceptDelete(String database) {
        return jdbcTemplate.queryForList(findAllDeviceExceptDelete.replace(databaseTag, database));
    }

    public List<Map<String, Object>> getDowntimeRecordList(String database) {
        jdbcTemplate.execute("set group_concat_max_len = 999999;");
        return jdbcTemplate.queryForList(getAllDowntimeRecord.replace(databaseTag, database));
        //// TODO: 2017/3/11
    }

    public List<Map<String, Object>> getMessages(String sql) {
        return jdbcTemplate.queryForList(sql);
    }

    public List<Map<String,Object>> findDeviceAndState(String database,String deviceId){
        return jdbcTemplate.queryForList(findDeviceAndState.replace(databaseTag,database),deviceId);
    }

    public List<Map<String,Object>> findAllDistinctDeviceModel(String database){
        return jdbcTemplate.queryForList(getDeviceModelDistinctQuery.replace(databaseTag,database));
    }

    public List<Map<String,Object>> findAllDistinctDeviceType(String database){
        return jdbcTemplate.queryForList(getDeviceTypeDistinctQuery.replace(databaseTag,database));
    }

    public List<Map<String,Object>> findAllDistinctDeviceGroup(String database){
        return jdbcTemplate.queryForList(getDeviceGroupDistinctQuery.replace(databaseTag,database));
    }

    public List<Map<String,Object>> findAllDistinctDeviceLine(String database){
        return jdbcTemplate.queryForList(getDeviceLineDistinctQuery.replace(databaseTag,database));
    }

    public List<Map<String,Object>> getListBySearch(String sql){
        return jdbcTemplate.queryForList(sql);
    }

    public List<Map<String,Object>> getLinesByTypeAndMode(String database,String type,String model){
        return jdbcTemplate.queryForList(getLinesByDistinctQuery.replace(databaseTag,database),model,type);
    }

    public List<Map<String,Object>> getGroupsByTypeAndMode(String database,String type,String model){
        return jdbcTemplate.queryForList(getGroupsByDistinctQuery.replace(databaseTag,database),model,type);
    }

    public List<Map<String,Object>> findAllLikeMtId(String database,String str){
        return jdbcTemplate.queryForList(findAllMTIdLikeCHarts.replace(databaseTag,database),String.format("%%%s%%", str));
    }

    public List<Map<String,Object>> findAllLikeIdle(String database,String str){
        return jdbcTemplate.queryForList(findAllIdleLikeCHarts.replace(databaseTag,database),String.format("%%%s%%", str));
    }

    @Transactional
    public void updateMessageStatus(String database,int status,String eventId,String mtid,Date stateTime){
        jdbcTemplate.update(updateMessageStatus.replace(databaseTag,database),status,eventId,mtid,stateTime);
    }


    public List<Map<String,Object>> findAllLikeMtType(String database,String str){
        return jdbcTemplate.queryForList(findAllMTTypeLike.replace(databaseTag,database),String.format("%%%s%%", str));
    }

    public List<Map<String,Object>> findAllLikeModel(String database,String str) {
        return jdbcTemplate.queryForList(findAllMTModelLike.replace(databaseTag,database),String.format("%%%s%%", str));
    }
    public List<Map<String,Object>> findAllLikeName(String database,String str) {
        return jdbcTemplate.queryForList(findAllMTNameLike.replace(databaseTag,database),String.format("%%%s%%", str));
    }
    public List<Map<String,Object>> findAllLikeGroup(String database,String str) {
        return jdbcTemplate.queryForList(findAllMTGroupLike.replace(databaseTag,database),String.format("%%%s%%", str));
    }
    public List<Map<String,Object>> findAllLikeLine(String database,String str) {
        return jdbcTemplate.queryForList(findAllMTLineLike.replace(databaseTag,database),String.format("%%%s%%", str));
    }
    public List<Map<String,Object>> findAllLikeMade(String database,String str) {
        return jdbcTemplate.queryForList(findAllMTMadeLike.replace(databaseTag,database),String.format("%%%s%%", str));
    }

    public List<Map<String,Object>> findDistinctAllGroups(String database) {
        return jdbcTemplate.queryForList(findAllDistinctGroups.replace(databaseTag,database));
    }

    public List<Map<String,Object>> findDistinctAllLines(String database) {
        return jdbcTemplate.queryForList(findAllDistinctLines.replace(databaseTag,database));
    }

    public List<Device> getDeviceForSearch(String sql) {
        return jdbcTemplate.query(sql,Device.getRowMapper());
    }

}
